﻿using DAL;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Data.OleDb;


namespace DLL
{
    public class AddStudent
    {

        private SQLHelper sqlhelper = null;
        public AddStudent()
        {
            sqlhelper = new SQLHelper();
        }

        /// <summary>
        /// 获取学院信息
        /// </summary>
        /// <returns></returns>
        public DataTable getCollegeInfo()
        {
            DataTable dt_College = null;
            List<CollegeEntity> listCollege = new List<CollegeEntity>();
            String sql = "select * from CollegeEntity";
           //dt_College = S
            dt_College = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt_College;

        }

        #region 根据学院名字选择对应学院下拉框的专业---吕德彪--2018
        /// <summary>
        /// 根据学院名字选择对应学院下拉框的专业---吕德彪--2018
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectMajorByCollegeName(TimeEntity timeEntity)
        {

            DataTable dt = new DataTable();            
            string sql = "select distinct a.major from StudentInfoEntity as a inner join CollegeEntity as b on a.collegeID = b.collegeID where b.collegeName = @collegeName";            
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter ("@collegeName",timeEntity.CollegeName )};
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 添加单个学生
        /// <summary>
        /// 添加单个学生
        /// </summary>
        /// <param name="studentInfo"></param>
        /// <returns></returns>
        public int AddStudentInfo(StudentInfoEntity studentInfo)
        {


            string sql = "Insert into StudentInfoEntity (studentID,CollegeID,studentName,major,grade,sex,majorClass,examPlaceID) values(@studentID,@CollegeID,@studentName,@major,@grade,@sex,@majorClass,@examPlaceID)";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("studentID",studentInfo.studentID),
                new SqlParameter("CollegeID",studentInfo.CollegeID),
                new SqlParameter("studentName",studentInfo.studentName),
                new SqlParameter("major",studentInfo.major),
                new SqlParameter("grade",studentInfo.grade),
                new SqlParameter("sex",studentInfo.sex),
                new SqlParameter("majorClass",studentInfo.majorClass),
                new SqlParameter("examPlaceID",studentInfo.examPlaceID)
            };           
            int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            return flag;
        }
        #endregion

        #region 查询IP填充下拉框,重复字段只保留一个---吕德彪--2018
        /// <summary>
        /// 查询IP填充下拉框---吕德彪--2018
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectIP()
        {

            DataTable dt = new DataTable();
            string sql = "select distinct ipName from IPEntity";
            SqlParameter[] paras = new SqlParameter[] {};
            dt = sqlhelper.ExecuteQuery(sql,paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 批量导入学生-吕德彪-2018 修改人：李金颖-修改考试地点为考场-2018-12-25 18:04:58
        /// <summary>
        /// 批量导入学生
        /// </summary>
        /// <returns></returns>
        public int AddGroupStudent(DataTable dt)
        {            
            string sql = "";
            int flag = 0;
            SqlParameter[] paras = null;
            for (int i = 1; i < dt.Rows.Count; i++)
            {
                //flag++;
                sql = "insert into StudentInfoEntity values(@学号,@学院ID,@考试地点,@姓名,@专业,@年级,@性别,@班级,@验证码,@是否在线)";
                paras = new SqlParameter[] {
                    //new SqlParameter("@学号", dtTemp.Tables[0].Rows[i][0].ToString()),
                    //new SqlParameter("@学院ID", dtTemp.Tables[0].Rows[i][1].ToString()),
                    //new SqlParameter("@考试地点", dtTemp.Tables[0].Rows[i][2].ToString()),
                    //new SqlParameter("@姓名", dtTemp.Tables[0].Rows[i][3].ToString()),
                    //new SqlParameter("@专业", dtTemp.Tables[0].Rows[i][4].ToString()),
                    //new SqlParameter("@年级", dtTemp.Tables[0].Rows[i][5].ToString()),
                    //new SqlParameter("@性别", dtTemp.Tables[0].Rows[i][6].ToString()),
                    //new SqlParameter("@班级", dtTemp.Tables[0].Rows[i][7].ToString())

                    new SqlParameter("@学号", dt.Rows[i][0].ToString()),
                    new SqlParameter("@学院ID", dt.Rows[i][1].ToString()),
                    new SqlParameter("@考试地点", dt.Rows[i][2].ToString()),
                    new SqlParameter("@姓名", dt.Rows[i][3].ToString()),
                    new SqlParameter("@专业", dt.Rows[i][4].ToString()),
                    new SqlParameter("@年级", dt.Rows[i][5].ToString()),
                    new SqlParameter("@性别", dt.Rows[i][6].ToString()),
                    new SqlParameter("@班级", dt.Rows[i][7].ToString()),
                     new SqlParameter("@验证码", dt.Rows[i][8].ToString()),
                      new SqlParameter("@是否在线", dt.Rows[i][9].ToString())
                    };
                flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);



            }
           
            return flag;
        }
        #endregion

        #region 查询批量导入的学生是否存在-吕德彪-2018.04.29
        /// <summary>
        /// 查询批量导入的学生是否存在
        /// </summary>
        /// <param name="studentInfo"></param>
        /// <returns></returns>
        public DataTable SelectStudent(StudentInfoEntity studentInfo)
        {          
            string sql = "select * from StudentInfoEntity where studentID = @studentID";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@studentID", studentInfo.studentID)
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion
    }
}
